CREATE TABLE upd_first (id serial PRIMARY KEY, name text);
INSERT INTO upd_first (name) VALUES ('weenie');
INSERT INTO upd_first (name) VALUES ('meenie');

-- basic functionality: I/U/D on a trivial view
CREATE VIEW v_first AS SELECT * FROM upd_first;
SELECT * FROM v_first;
INSERT INTO v_first VALUES (3, 'meenie');
DELETE FROM v_first WHERE id = 2;
UPDATE v_first SET name = 'eenie' WHERE id = 1;
SELECT * FROM v_first;

-- fail because view doesn't have the DEFAULT clause
INSERT INTO v_first (name) VALUES ('phooey');

-- so set it
ALTER TABLE v_first ALTER id
SET DEFAULT nextval(pg_get_serial_sequence('upd_first', 'id'));

-- try again ... this one fails because the sequence got left behind
INSERT INTO v_first (name) VALUES ('doobie');
-- but this one should work
INSERT INTO v_first (name) VALUES ('minie');
SELECT * FROM v_first;

-- try a view on a view, with columns reversed
CREATE VIEW v_v_first as SELECT name, id FROM v_first;
SELECT * FROM v_v_first;
ALTER TABLE v_v_first ALTER id
SET DEFAULT nextval(pg_get_serial_sequence('upd_first', 'id'));
INSERT INTO v_v_first VALUES ('tangerine', DEFAULT);

-- Let's try an "horizontally-constrained" view of the table.  We can't trust
-- the real CURRENT_USER to return a constant, so fake it.
create function fake_curr_usr() RETURNS name
AS $$ SELECT 'regression'::name $$ LANGUAGE sql;

ALTER TABLE upd_first ADD COLUMN usr name DEFAULT fake_curr_usr();

CREATE VIEW v_second AS SELECT * FROM upd_first WHERE usr = fake_curr_usr();
ALTER TABLE v_second ALTER id
SET DEFAULT nextval(pg_get_serial_sequence('upd_first', 'id'));

-- this one works because the view was not created with check option
INSERT INTO v_second (name, usr) VALUES ('doe', 'nobody');

-- but let's see what happens with one?
CREATE VIEW v_third AS SELECT * FROM upd_first WHERE usr = fake_curr_usr()
WITH CHECK OPTION;
ALTER TABLE v_third ALTER id
SET DEFAULT nextval(pg_get_serial_sequence('upd_first', 'id'));
-- these fail: violation of the view condition
INSERT INTO v_third (name, usr) VALUES ('viper', 'nobody');
UPDATE v_third set name = 'piggy', usr = 'usurper' WHERE id = 3;
-- these work
INSERT INTO v_third (name, usr) VALUES ('tiger', fake_curr_usr());
UPDATE v_third SET name = 'moe' WHERE id = 5;

-- now try a vertically-constrained view, i.e. not all columns
CREATE VIEW v_fourth as SELECT id, name FROM upd_first;
-- fails because id doesn't have a default
INSERT INTO v_fourth (name) VALUES ('on');
ALTER TABLE v_fourth ALTER id
SET DEFAULT nextval(pg_get_serial_sequence('upd_first', 'id'));
-- fails because view doesn't have column usr
INSERT INTO v_fourth (id, name, usr) VALUES (default, 'FROM', 'gross');
-- works, with defaults for id and usr
INSERT INTO v_fourth (name) VALUES ('by');
UPDATE v_fourth set name = 'catcha' WHERE id = 6;

-- no UPDATE rules for a view on a join (even if there's only one base table)
CREATE VIEW v_fifth as SELECT id, v_first.name, usr
FROM v_first JOIN v_second USING (id);
INSERT INTO v_fifth DEFAULT VALUES;

-- no rules for a view on a query with two FROM items
CREATE VIEW v_sixth AS SELECT v_first.id, v_first.name, usr
FROM v_first, v_second
WHERE v_first.id = v_second.id;
INSERT INTO v_sixth DEFAULT VALUES;

-- test WITH LOCAL CHECK OPTION
CREATE VIEW v_seventh AS SELECT * FROM upd_first WHERE usr = fake_curr_usr() WITH CHECK OPTION;
CREATE VIEW v_eighth AS SELECT * FROM v_seventh WHERE id > 4 WITH LOCAL CHECK OPTION;
ALTER TABLE v_eighth ALTER id
SET DEFAULT nextval(pg_get_serial_sequence('upd_first', 'id'));

INSERT INTO v_eighth (name, usr) VALUES ('jut', fake_curr_usr());

-- we can't violate v_eighth condition
INSERT INTO v_eighth (id) values (2);
UPDATE v_eighth SET id = 2 WHERE name = 'jut';
-- but we can violate v_seventh's
UPDATE v_eighth SET usr = 'someone' WHERE name = 'jut';
INSERT INTO v_eighth (name, usr) VALUES ('kvu', 'anyone');

-- the violations don't occur on a view with cascaded check option:
CREATE VIEW v_ninth AS SELECT * FROM v_seventh WHERE id > 4 WITH CASCADED CHECK OPTION;
INSERT INTO v_ninth (id) values (2);
INSERT INTO v_ninth (name, usr) VALUES ('archaeopterix', fake_curr_usr());
INSERT INTO v_ninth (name, usr) VALUES ('pteranodon', 'anyone');
UPDATE v_ninth SET id = 2 WHERE name = 'jut';
UPDATE v_ninth SET usr = 'someone' WHERE name = 'jut';

DELETE FROM v_ninth WHERE usr = 'nobody';
DELETE FROM v_eighth WHERE usr = 'nobody';
DELETE FROM upd_first WHERE usr = 'anyone';

CREATE VIEW v_tenth AS SELECT * FROM upd_first WHERE id % 2 = 0 AND usr <> fake_curr_usr();
UPDATE v_tenth set name = 'its', usr = fake_curr_usr() WHERE usr = 'someone';
UPDATE v_tenth set name = 'toe' WHERE usr = 'anyone';

SELECT * FROM v_tenth ORDER BY id;
SELECT * FROM upd_first ORDER BY id;

CREATE VIEW v_tenth AS SELECT * FROM generate_series(1, 5);